﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class menu_check : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string userid;
        if ( Session["UserId"] == null || Session["UserIdentity"].ToString()!="0")
        {
            Response.Redirect("Default.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
        else 
            userid = Session["UserId"].ToString();
        SearchUserInfo(userid);
        return;
    }
    protected void SearchUserInfo(string uid)
    {
        //用户信息
        string name, userid, sex="0", college, grade="0";
        //体测成绩信息
        string HeightValue="0", WeightValue="0", VitalValue="0", StaminaValue="0", StrengthValue="0", SpeedValue="0";
        string StaminaName="", StrengthName="", SpeedName="";
        string HeightWeightMark = "0", HeightWeightLevel = "", VitalWeightMark = "0", VitalWeightLevel = "", StaminaMark = "0", StaminaLevel = "", StrengthMark = "0", StrengthLevel = "", SpeedMark = "0", SpeedLevel = "";
        int VitalHeavyWeight = 20, StaminaHeavyWeight = 30, StrengthHeavyWeight = 20,SpeedHeavyWeight=20;
        string JudgeInfo = "",Recipe="";
        //身高低于表中所列出的最低身高段的下限值时，身高每低1厘米，实测体重需加上0.5公斤，
        //实测身高需加上1厘米，再查表确定分值。
        //身高高于表中所列出的最高身高段时，身高每高1厘米，其实测体重需减去0.9公斤，
        //实测身高需减去1厘米，再查表确定分值。
        //男生最低最高为144，190.9cm ，女生最低最高为140.0，186.0cm
        float femaleLow = 144.0F, femaleHigh = 190.9F, maleLow = 140.0F, maleHigh = 186.9F;
        float LowerW=0.5F,HigherW=0.9F;
        try
        {
            SqlConnection Sqlconn = new SqlConnection("Data Source=Localhost;Integrated Security=SSPI;Initial Catalog=CSHDB");
            Sqlconn.Open();
            DateTime date = new DateTime(2010, 1, 1, 0, 0, 0);
            string sqlString = "select Name,CollegeName,Sex,Grade from UserDetail where UserId='" + uid + "'";
            SqlCommand sqlcmd = new SqlCommand(sqlString, Sqlconn);
            sqlcmd.Connection = Sqlconn;
            sqlcmd.CommandText = sqlString;
            SqlDataReader reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                //为用户信息赋值
                name = reader.GetValue(0).ToString();
                userid = uid;
                college = reader.GetValue(1).ToString();
                sex = reader.GetValue(2).ToString();
                grade = reader.GetValue(3).ToString();

                //为给相应Label赋值
                UserNameLabel.Text = name.Trim();
                UserIdLabel.Text = userid.Trim();
                UserCollegeLabel.Text = college.Trim();
                if (sex.ToLower() == "true")
                {
                    UserSexLabel.Text = "女";
                }
                else
                {
                    UserSexLabel.Text = "男";
                }
                UserGradeLabel.Text = grade.Trim();
            }

            //获得所有当年体测信息Value值
            sqlString = "select Weight,Height,VitalCapacity,StaminaName,Stamina,StrengthName,Strength,SpeedName,Speed from PhysiqueTestResultView where UserId='" + uid + "' AND TestDate='" + date.ToString("d") + "'";
            reader.Dispose();
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                //为体测信息赋值
                WeightValue = reader.GetValue(0).ToString();
                HeightValue = reader.GetValue(1).ToString();
                VitalValue = reader.GetValue(2).ToString();
                StaminaName = reader.GetValue(3).ToString();
                StaminaValue = reader.GetValue(4).ToString();
                StrengthName = reader.GetValue(5).ToString();
                StrengthValue = reader.GetValue(6).ToString();
                SpeedName = reader.GetValue(7).ToString();
                SpeedValue = reader.GetValue(8).ToString();

                //改变相应Label的Text值，显示出用户体测信息
                HeightWeightValueLabel.Text = HeightValue + "CM/" + WeightValue + "KG";
                VitalWeightValueLabel.Text = VitalValue + "ML/" + WeightValue + "KG";
                StaminaLabel.Text = StaminaName.Trim();
                StaminaValueLable.Text = StaminaValue.Trim();
                StrengthLabel.Text = StrengthName.Trim();
                StrengthValueLabel.Text = StrengthValue.Trim();
                SpeedLabel.Text = SpeedName.Trim();
                SpeedValueLabel.Text = SpeedValue.Trim();
            }

            //对如果身高不在可以判断的范围内。
            //则身高和体重进行处理，以使之在可查询范围内。
            float height, weight;
            height = float.Parse(HeightValue);
            weight = float.Parse(WeightValue);
            if (height < femaleLow && sex.ToLower() == "false")
            {
                while (height >= femaleLow)
                {
                    height += 1;
                    weight += LowerW;
                }
            }
            else if (height < maleLow && sex.ToLower() == "true")
            {
                while (height >= maleLow)
                {
                    height += 1;
                    weight += LowerW;
                }
            }
            else if (height > femaleHigh && sex.ToLower() == "false")
            {
                while (height <= femaleHigh)
                {
                    height -= 1;
                    weight -= HigherW;
                }
            }
            else if (height > maleHigh && sex.ToLower() == "true")
            {
                while (height <= femaleHigh)
                {
                    height -= 1;
                    weight -= HigherW;
                }
            }

            HeightValue = height.ToString();
            WeightValue = weight.ToString();

            //为体测数据进行打分

            //获取身高/体重指数的分数和等级
            reader.Dispose();
            sqlString = "select Mark,Level from HeightWeightLevelTable where LowHeight<=" + HeightValue + " and HighHeight>=" + HeightValue + " and sex='" + sex + "' and " + WeightValue + ">=LowWeight and " + WeightValue + "<=HighWeight";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();

                HeightWeightMark = reader.GetValue(0).ToString();
                HeightWeightLevel = reader.GetValue(1).ToString();

                HeightWeightMarkLabel.Text = HeightWeightMark;
                HeightWeightLevelLabel.Text = HeightWeightLevel;
            }

            //获取肺活量体重指数的分数和等级,并为相应Label赋值
            reader.Dispose();
            float VitalWeightValue = float.Parse(VitalValue) / float.Parse(WeightValue);
            sqlString = "select Mark,Level,HeavyWeight from TargetLevel where TestName='肺活量体重指数' and LowerLimit<=" + VitalWeightValue.ToString() + " and UpLimit>" + VitalWeightValue.ToString() + " and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();

                VitalWeightMark = reader.GetValue(0).ToString();
                VitalWeightLevel = reader.GetValue(1).ToString();
                VitalHeavyWeight = int.Parse(reader.GetValue(2).ToString());

                VitalWeightMarkLabel.Text = VitalWeightMark;
                VitalWeightLevelLabel.Text = VitalWeightLevel;
            }

            //获取耐力项目的分数和等级，并为相应Label赋值
            reader.Dispose();
            sqlString = "select Mark,Level,HeavyWeight from TargetLevel where TestName='" + StaminaName + "' and LowerLimit<=" + StaminaValue + " and UpLimit>" + StaminaValue + " and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();

                StaminaMark = reader.GetValue(0).ToString();
                StaminaLevel = reader.GetValue(1).ToString();
                StaminaHeavyWeight = int.Parse(reader.GetValue(2).ToString());

                StaminaMarkLabel.Text = StaminaMark;
                StaminaLevelLabel.Text = StaminaLevel;
            }

            //获取柔韧力量项目的分数和等级，并为相应的Label赋值
            reader.Dispose();
            //如果是握力项目的话，需要查询的是握力体重指数
            if (StrengthName.Trim() == "握力")
            {
                StrengthValue = (float.Parse(StrengthValue) * 100 / float.Parse(WeightValue)).ToString();
                StrengthName = "握力体重指数";
            }
            sqlString = "select Mark,Level,HeavyWeight from TargetLevel where TestName='" + StrengthName + "' and LowerLimit<=" + StrengthValue + " and UpLimit>" + StrengthValue + " and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();

                StrengthMark = reader.GetValue(0).ToString();
                StrengthLevel = reader.GetValue(1).ToString();
                StrengthHeavyWeight = int.Parse(reader.GetValue(2).ToString());

                StrengthMarkLabel.Text = StrengthMark;
                StrengthLevelLabel.Text = StrengthLevel;
            }

            //获取速度灵巧度项目的分数和等级，并为相应的Label赋值
            reader.Dispose();
            sqlString = "select Mark,Level,HeavyWeight from TargetLevel where TestName='" + SpeedName + "' and LowerLimit<=" + SpeedValue + " and UpLimit>" + SpeedValue + " and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();

                SpeedMark = reader.GetValue(0).ToString();
                SpeedLevel = reader.GetValue(1).ToString();
                SpeedHeavyWeight = int.Parse(reader.GetValue(2).ToString());

                SpeedMarkLabel.Text = SpeedMark;
                SpeedLevelLabel.Text = SpeedLevel;
            }


            //算总分,并给显示分数和等级
            int TotalMark = (int.Parse(HeightWeightMark) * 10 + int.Parse(VitalWeightMark) * VitalHeavyWeight + int.Parse(StaminaMark) * StaminaHeavyWeight + int.Parse(StrengthMark) * StrengthHeavyWeight + int.Parse(SpeedMark) * SpeedHeavyWeight) / 100;
            TotalMarkLabel.Text = TotalMark.ToString();
            TotalLevelLabel.Text = JudgeLevel(TotalMark);
            int count = 1;
            //获得体测评价
            //获得升高体重指数评价
            reader.Dispose();
            sqlString = "select JudgeInfo from JudgeInfoTable where TestName='身高体重指数' and Level='" + HeightWeightLevel + "' and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                JudgeInfo += count.ToString() + ") ";
                count++;
                JudgeInfo += reader.GetValue(0).ToString();
                JudgeInfo += "<br/>";
            }

            //获得肺活量体重指数评价
            reader.Dispose();
            sqlString = "select JudgeInfo from JudgeInfoTable where TestName='肺活量体重指数' and Level='" + VitalWeightLevel + "' and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                JudgeInfo += count.ToString() + ") ";
                count++;
                reader.Read();
                JudgeInfo += reader.GetValue(0).ToString();
                JudgeInfo += "<br/>";
            }

            //获得耐力项目评价
            reader.Dispose();
            sqlString = "select JudgeInfo from JudgeInfoTable where TestName='" + StaminaName + "' and Level='" + StaminaLevel + "' and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                JudgeInfo += count.ToString() + ") ";
                count++;
                JudgeInfo += reader.GetValue(0).ToString();
                JudgeInfo += "<br/>";
            }

            //获得柔韧性力量项目评价
            reader.Dispose();
            sqlString = "select JudgeInfo from JudgeInfoTable where TestName='" + StrengthName + "' and Level='" + StrengthLevel + "' and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                JudgeInfo += count.ToString() + ") ";
                count++;
                JudgeInfo += reader.GetValue(0).ToString();
                JudgeInfo += "<br/>";
            }


            //获得速度灵巧性项目评价
            reader.Dispose();
            sqlString = "select JudgeInfo from JudgeInfoTable where TestName='" + SpeedName + "' and Level='" + SpeedLevel + "' and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                JudgeInfo += count.ToString() + ") ";
                count++;
                JudgeInfo += reader.GetValue(0).ToString();
                JudgeInfo += "<br/>";
            }

            CheckLabel.Text = JudgeInfo;

            count = 1;
            //获得学生运动处方

            //获得身体形态状态的运动处方
            reader.Dispose();
            sqlString = "select Content from Recipe where ItemCatagory='身体形态' and Level='" + HeightWeightLevel + "' and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                Recipe += "<b>" + "身体形态运动处方：</b><br />";
                count++;
                Recipe += reader.GetValue(0).ToString();
                Recipe += "<br/>";
            }

            //获得耐力项目运动处方
            reader.Dispose();
            sqlString = "select Content from Recipe where ItemCatagory='耐力项目' and Level='" + StaminaLevel + "' and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                Recipe += "<b>" + "耐力运动处方：</b><br />";
                count++;
                Recipe += reader.GetValue(0).ToString();
                Recipe += "<br/>";
            }

            //获得柔韧项目运动处方
            if (StrengthName == "握力体重指数")
            {
                reader.Dispose();
                sqlString = "select Content from Recipe where ItemCatagory='力量项目' and Level='" + StrengthLevel + "' and Sex='" + sex + "'";
                sqlcmd.CommandText = sqlString;
                reader = sqlcmd.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    Recipe += "<b>" + "力量运动处方：</b><br />";
                    count++;
                    Recipe += reader.GetValue(0).ToString();
                    Recipe += "<br/>";
                }
            }
            else
            {
                reader.Dispose();
                sqlString = "select Content from Recipe where ItemCatagory='柔韧项目' and Level='" + StrengthLevel + "' and Sex='" + sex + "'";
                sqlcmd.CommandText = sqlString;
                reader = sqlcmd.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    Recipe += "<b>" + "柔韧运动处方：</b><br />";
                    count++;
                    Recipe += reader.GetValue(0).ToString();
                    Recipe += "<br/>";
                }

            }

            //获得速度灵巧性项目运动处方
            reader.Dispose();
            sqlString = "select Content from Recipe where ItemCatagory='速度灵巧项目' and Level='" + SpeedLevel + "' and Sex='" + sex + "'";
            sqlcmd.CommandText = sqlString;
            reader = sqlcmd.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                Recipe += "<b>" + "灵巧性运动处方：</b><br />";
                count++;
                Recipe += reader.GetValue(0).ToString();
                Recipe += "<br/>";
            }
            Recipe = Recipe.Replace("|", "<br/>");
            RecipeLabel.Text = Recipe;

            Sqlconn.Close();
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message;
            Session["AtPage"] = "check.aspx";
            Response.Redirect("s_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }
    private string JudgeLevel(int mark)
    {
        if (mark<60)
        {
            return "不及格";
        }
        else if (mark>=60 && mark<75)
        {
            return "及格";
        }
        else if (mark>=75 && mark<85)
        {
            return "良好";
        }
        else
        {
            return "优秀";
        }
    }
}
